package com.daily.DailyStudy.常用工具类.excel;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

public final class ExcelKit {
	private static final String 换行符 = "\r\n";
	private static final Logger logger = LoggerFactory.getLogger(ExcelKit.class);
	/**
	 * 读取excel文件返回所有数据
	 * 
	 * 
	 * @param is
	 *            excel文件输入流
	 * @return [工作表 list] [行数据 list] [单元格 map<key:列名如[A,B,C]，value:单元格值>]
	 */
	public static List<List<Map<String, String>>> getAllSheetData(InputStream is) {

		List<List<Map<String, String>>> reList = Lists.newArrayList();
		try {
			Workbook workbook = POIExcelReader.getWorkBookByStream(is);
			for (int i = 1; i <= workbook.getNumberOfSheets(); i++) {
				reList.add(POIExcelReader.getSheetDataMap(POIExcelReader.getSheetByNum(workbook, i)));
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println(e.getMessage());
		}

		return reList;
	}

	/**
	 * 获取某工作表数据
	 * 
	 * @param is
	 * @param sheet
	 *            工作表序号，从 1 开始
	 * @return [行数据 list] [单元格 map<key:列名如[A,B,C]，value:单元格值>]
	 */
	public static List<Map<String, String>> getSheetData(InputStream is, int sheet) {
		try {
			Workbook workbook = POIExcelReader.getWorkBookByStream(is);
			return POIExcelReader.getSheetDataMap(POIExcelReader.getSheetByNum(workbook, sheet));
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println(e.getMessage());
		}
		return Lists.newArrayList();
	}

	/**
	 * 获取某工作表数据，以第一行作为列名
	 * 
	 * @param is
	 * @param sheet
	 *            工作表序号，从 1 开始
	 * @return [行数据 list] [单元格 map<key:列名，value:单元格值>]
	 */
	public static List<Map<String, String>> getSheetDataWithTitle(InputStream is, int sheet) {
		try {
			Workbook workbook = POIExcelReader.getWorkBookByStream(is);
			List<Map<String, String>> datas = POIExcelReader
					.getSheetDataMap(POIExcelReader.getSheetByNum(workbook, sheet));

			// 将第一行数据取出并从列表中删除
			Map<String, String> title = Maps.newHashMap(datas.get(0));
			datas.remove(0);
			for (Map<String, String> data : datas) {
				Map<String, String> newData = Maps.newHashMap();

				// 迭代每个行数据map，将原来使用列名[A,B,C]作为key，改为使用标题行数据作为列名
				Iterator<String> keys = data.keySet().iterator();
				while (keys.hasNext()) {
					String key = keys.next();
					newData.put(title.get(key), data.get(key));
				}

				data.putAll(newData);
			}

			return datas;
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println(e.getMessage());
		}
		return Lists.newArrayList();
	}

    public static void main(String[] args) throws Exception{
        InputStream is = new FileInputStream(new File("C:\\Users\\guany\\Desktop\\0830报交通.xlsx"));
        //获取表格内容 [行数据 list] [单元格 map<key:列名如[A,B,C]，value:单元格值>]
        List<Map<String, String>> excelList = ExcelKit.getSheetData(is,1);
        //获取Excel的A列第2行的值
        System.out.println("excel值: "+excelList.get(1).get("A"));;
    }
}
